<?php
class sis_report_model extends MY_Model {
	var $model = 'sis_report';
	
	function GetSimpananJSON()
	{
		$ls_sql	= "	SELECT  SAL.ID_ANGGOTA, TO_CHAR(SAL.TGL_SALDO,'DD/MM/YYYY') TGL_SALDO_TXT, 
                            SAL.SALDO_POKOK, SAL.SALDO_WAJIB, SAL.SALDO_SUKARELA, SAL.SALDO_BUNGA, SAL.SALDO_DENDA, SAL.SALDO_SHU, SAL.SALDO_SIMPANAN,
                            PRS.FULL_NAME, JNSAGT.NAMA_JENIS_ANGGOTA, COMP.NAMA_PERUSAHAAN
                    FROM    SIS_SALDO_ANGGOTA_BULANAN SAL
							JOIN SIS_ANGGOTA AGT ON SAL.ID_ANGGOTA = AGT.ID_ANGGOTA
							JOIN SW_PERSON PRS ON PRS.PERSON_ID = AGT.PERSON_ID
							LEFT JOIN SIS_SETUP_JENIS_ANGGOTA JNSAGT ON JNSAGT.KD_JENIS_ANGGOTA = AGT.KD_JENIS_ANGGOTA
							LEFT JOIN SW_PERUSAHAAN COMP ON AGT.ID_PERUSAHAAN = COMP.ID_PERUSAHAAN
					WHERE	AGT.DELETE_DATE IS NULL
							".(post('kd_jenis_anggota') != '' ? " AND AGT.KD_JENIS_ANGGOTA = '".post('kd_jenis_anggota')."' " : '')."
							".(post('id_anggota') != '' ? " AND AGT.ID_ANGGOTA = '".post('id_anggota')."' " : '')."
							".(post('tgl_awal') != '' ? " AND SAL.TGL_SALDO >= TO_DATE('".post('tgl_awal')."','DD/MM/YYYY') " : '')."
							".(post('tgl_akhir') != '' ? " AND SAL.TGL_SALDO <= TO_DATE('".post('tgl_akhir')."','DD/MM/YYYY') " : '')."
							AND (UPPER(NVL(AGT.ID_ANGGOTA,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(JNSAGT.NAMA_JENIS_ANGGOTA,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(PRS.FULL_NAME,'')) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(NVL(PRS.MOTHER_NAME,'')) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(NVL(PRS.IDENTITY_NUMBER,'')) LIKE '%".strtoupper(post('keyword'))."%') 
					ORDER BY ".(post('sort')=='' ? 'SAL.TGL_SALDO DESC, AGT.ID_ANGGOTA' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));
					
		return json_encode( $this->paging($ls_sql) );
	}
	
	function GetPinjamanJSON()
	{
		$ls_sql	= "	SELECT	A.ID_PINJAMAN, A.ID_ANGGOTA, TO_CHAR(A.TGL_PERMOHONAN,'DD/MM/YYYY') TGL_PERMOHONAN, A.JML_PINJAMAN, A.LAMA_PINJAMAN, 
							A.PERSEN_BUNGA, A.KETERANGAN, A.KODE_JENIS_PINJAMAN,
							NVL(A.ST_APPROVE,'0') ST_APPROVE, F_SIS_STATUS_PINJAMAN(ID_PINJAMAN) STATUS_APPROVE,
							TO_CHAR(A.TGL_APPROVE,'DD/MM/YYYY') TGL_APPROVE, A.APPROVE_USER, 
							A.ST_TRANSFER, TO_DATE(A.TGL_TRANSFER,'DD/MM/YYYY') TGL_TRANSFER, A.BUKTI_TRANSFER, 
							A.ST_LUNAS, TO_CHAR(A.TGL_LUNAS,'DD/MM/YYYY') TGL_LUNAS,
							C.FULL_NAME, C.IDENTITY_NUMBER, D.NAMA_JENIS_ANGGOTA, E.NAMA_BANK,
							G.ID_PERUSAHAAN, G.KODE_PERUSAHAAN, G.NAMA_PERUSAHAAN,
							F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'POKOK', SYSDATE) SIMPANAN_POKOK,
							F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'WAJIB', SYSDATE) SIMPANAN_WAJIB,
							F_SIS_GET_SALDO_ANGGOTA(A.ID_ANGGOTA, 'SUKARELA', SYSDATE) SIMPANAN_SUKARELA
					FROM	SIS_PINJAMAN_ANGGOTA A
							LEFT JOIN SIS_ANGGOTA B ON A.ID_ANGGOTA = B.ID_ANGGOTA
							LEFT JOIN SW_PERSON C ON B.PERSON_ID = C.PERSON_ID
							LEFT JOIN SIS_SETUP_JENIS_ANGGOTA D ON B.KD_JENIS_ANGGOTA = D.KD_JENIS_ANGGOTA
							LEFT JOIN KEU_BANK E ON E.KD_BANK = A.KD_BANK
							LEFT JOIN SW_PERUSAHAAN G ON B.ID_PERUSAHAAN = G.ID_PERUSAHAAN
					WHERE	(UPPER(NVL(A.ID_PINJAMAN,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(A.ID_ANGGOTA,'')) LIKE '%".strtoupper(post('keyword'))."%' 
							OR UPPER(NVL(C.FULL_NAME,'')) LIKE '%".strtoupper(post('keyword'))."%'
							OR UPPER(NVL(C.IDENTITY_NUMBER,'')) LIKE '%".strtoupper(post('keyword'))."%') 
							".(post('kd_jenis_anggota') != '' ? " AND B.KD_JENIS_ANGGOTA = '".post('kd_jenis_anggota')."' " : '')."
							".(post('id_anggota') != '' ? " AND AGT.ID_ANGGOTA = '".post('id_anggota')."' " : '')."
							".(post('tgl_awal') != '' ? " AND A.TGL_PERMOHONAN >= TO_DATE('".post('tgl_awal')."','DD/MM/YYYY') " : '')."
							".(post('tgl_akhir') != '' ? " AND A.TGL_PERMOHONAN <= TO_DATE('".post('tgl_akhir')."','DD/MM/YYYY') " : '')."
							
							AND UPPER(NVL(g.id_perusahaan,'')) LIKE '%".strtoupper(post('id_perusahaan'))."%'
							AND UPPER(NVL(g.kode_perusahaan,'')) LIKE '%".strtoupper(post('kode_perusahaan'))."%'
							AND UPPER(NVL(g.nama_perusahaan,'')) LIKE '%".strtoupper(post('nama_perusahaan'))."%'
					ORDER BY ".(post('sort')=='' ? "NVL(A.ST_APPROVE,'0') ASC, A.TGL_PERMOHONAN" : post('sort'))." ".(post('order')=='' ? 'desc' : post('order'));
					
		return json_encode( $this->paging($ls_sql) );
	}
	
	function GetRincianPotonganJSON()
	{
		$ls_sql	= "	SELECT  TGH.ID_ANGGOTA, TO_CHAR(TGH.TGL_TAGIHAN,'DD/MM/YYYY') TGL_TAGIHAN, 
							TGH.SIMP_WAJIB, TGH.SIMP_POKOK, TGH.SIMP_SUKARELA, TGH.CICILAN_PINJAMAN, TGH.TOTAL_TAGIHAN, TGH.SISA_TAGIHAN, 
							TGH.ST_LUNAS, TO_CHAR(TGH.TGL_LUNAS,'DD/MM/YYYY') TGL_LUNAS,
							PRS.FULL_NAME, JNSAGT.NAMA_JENIS_ANGGOTA
					FROM    SIS_TAGIHAN_ANGGOTA TGH
							JOIN SIS_ANGGOTA AGT ON TGH.ID_ANGGOTA = AGT.ID_ANGGOTA
							JOIN SW_PERSON PRS ON PRS.PERSON_ID = AGT.PERSON_ID
							LEFT JOIN SIS_SETUP_JENIS_ANGGOTA JNSAGT ON JNSAGT.KD_JENIS_ANGGOTA = AGT.KD_JENIS_ANGGOTA
					WHERE	AGT.DELETE_DATE IS NULL
							AND UPPER(NVL(PRS.FULL_NAME,'')) LIKE '%".strtoupper(post('full_name'))."%'
							".(post('kd_jenis_anggota') != '' ? " AND AGT.KD_JENIS_ANGGOTA = '".post('kd_jenis_anggota')."' " : '')."
							".(post('id_anggota') != '' ? " AND AGT.ID_ANGGOTA = '".post('id_anggota')."' " : '')."
							".(post('tgl_awal') != '' ? " AND TGH.TGL_TAGIHAN >= TO_DATE('".post('tgl_awal')."','DD/MM/YYYY') " : '')."
							".(post('tgl_akhir') != '' ? " AND TGH.TGL_TAGIHAN <= TO_DATE('".post('tgl_akhir')."','DD/MM/YYYY') " : '')."
					ORDER BY ".(post('sort')=='' ? 'TGH.TGL_TAGIHAN ASC, AGT.ID_ANGGOTA' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));
		
		return json_encode( $this->paging($ls_sql) );
	}
}
?>